import oracle.jdbc.OracleConnection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.sql.*;
import java.util.Properties;

@SpringBootApplication
public class Main {

    final static Logger LOG = LoggerFactory.getLogger(Main.class);

    private static final int MYSQL_DRIVER=1;
    private static final int ORACLE_DRIVER=2;
    private static final int DM7_DRIVER=3;
  private static final int SQLServer_DRIVER=4;
  private static  final int DB2_DRIVER=5;

  private static int  currentDriver=MYSQL_DRIVER;
    /****
     *
     java -jar jdbc-tester-1.0.jar "sys as sysdba"  1 jdbc:oracle:thin:@//192.168.11.160:1521/orcl  A  null

     eg:
     java -jar jdbc-tester-1.0.jar "sys as sysdba"  1 jdbc:oracle:thin:@//192.168.11.160:1521/orcl B hcj.NI
     */
    public static void main(String[] args) throws ClassNotFoundException {

        for (int i = 0; i < args.length; i++) {
            LOG.info("arg {} = {}", i, args[i]);
        }


        if (args.length != 5) {
            LOG.error("Invalid number of arguments: Must provide 5 arguments in the format: <schema_name> <schema_password> "
                + "jdbc:oracle:thin:@//<host>:<port>/<SID> <mode> <destObject>");
            return;
        }

        Properties properties = new Properties();
        properties.setProperty("user", args[0]);
        properties.setProperty("password", args[1]);
        properties.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CONNECT_TIMEOUT, "8000");

        String jdbcURL=args[2];
        String mode=args[3];
        String destObject=args[4];
        if(jdbcURL.contains("jdbc:oracle")){
          Class.forName("oracle.jdbc.driver.OracleDriver");
          LOG.info("loaded OracleDriver!");
          currentDriver=ORACLE_DRIVER;
        }else if(jdbcURL.contains("jdbc:mysql")){
          Class.forName("com.mysql.jdbc.Driver");
          LOG.info("loaded MySqlDriver!");
        }

      if(mode.equalsIgnoreCase("A")){
        /***
         * 连接测试
         */
        testConnection(jdbcURL,properties);
      }
      else if(mode.equalsIgnoreCase("B")){
        /***
         * 表结构测试
         */
        testTable(jdbcURL,destObject,properties);
        }else if(mode.equalsIgnoreCase("C")){
        /***
         * 存储过程测试
         */
        testProcedure(jdbcURL,destObject,properties);
        }else{

        }
    }
  private static void testConnection(String url,Properties properties){
    LOG.info("****** Starting testConnection *******");
    //String sqlQuery = "select sysdate from dual";

    String sqlQuery=getTestConnectionSQL();
    try(Connection conn = DriverManager.getConnection(url, properties)){
      conn.setAutoCommit(false);
      Statement statement = conn.createStatement();
      LOG.info("Running SQL query: [{}]", sqlQuery);
      ResultSet resultSet = statement.executeQuery(sqlQuery);

      while (resultSet.next()) {
        LOG.info("Result of SQL query: [{}]", resultSet.getString(1));
      }
      LOG.info("testConnection successful!");
    }catch (Exception e){
      LOG.info("testConnection error:"+e.getMessage());
    }
  }
  private static String getTestConnectionSQL(){
    String sqlQuery = "select sysdate() as  now";
    switch (currentDriver){
      case ORACLE_DRIVER:
        sqlQuery= "select sysdate from dual";
      case DB2_DRIVER:
        break;
      case SQLServer_DRIVER:
        break;
      case DM7_DRIVER:
        break;
    }
    return sqlQuery;
  }

  private static String getTestTableSQL(String destObject){
    String sqlQuery = "select * FROM  "+destObject+" limit 0,10";
    switch (currentDriver){
      case ORACLE_DRIVER:
        sqlQuery= "SELECT * FROM (SELECT A.*, ROWNUM RN  FROM (SELECT * FROM "+destObject+") A  WHERE ROWNUM <= 10)WHERE RN >0";
      case DB2_DRIVER:
        break;
      case SQLServer_DRIVER:
        break;
      case DM7_DRIVER:
        break;
    }
    return sqlQuery;
  }
    private static void testTable(String url,String destObject,Properties properties){
            LOG.info("****** Starting test destObject *******");
            //String sqlQuery = "select * FROM  "+destObject;
            String sqlQuery=getTestTableSQL(destObject);

            try(Connection conn = DriverManager.getConnection(url, properties)){
            conn.setAutoCommit(false);
            Statement statement = conn.createStatement();
            LOG.info("Running SQL query: [{}]", sqlQuery);
            ResultSet resultSet = statement.executeQuery(sqlQuery);
              ResultSetMetaData columnMetaData=resultSet.getMetaData();

            int columnCount=resultSet.getMetaData().getColumnCount();
              StringBuffer sb=new StringBuffer("");
            for(int index=1;index<=columnCount;index++){
              sb.append(columnMetaData.getColumnName(index)==null?"":columnMetaData.getColumnName(index).trim());
              sb.append("|");
            }
              LOG.info("ColumnName of Tale: [{}]", sb.substring(0,sb.length()-1).toString());
            while (resultSet.next()) {
              sb=new StringBuffer("");
              for( int i=1;i<=columnCount;i++){
                sb.append(resultSet.getString(i)==null?"":resultSet.getString(i).trim());
                sb.append(",");
              }
                LOG.info("Result of SQL query: [{}]", sb.substring(0,sb.length()-1).toString());
            }
            LOG.info("testTable successful!");
        }catch (Exception e){
                LOG.info("testTable error:"+e.getMessage());
            }
    }
    private static void testProcedure(String url,String destObject,Properties properties){
      {
        LOG.info("****** Starting test destObject *******");
        String sqlQuery = "{call "+destObject+" }";

        try(Connection conn = DriverManager.getConnection(url, properties)){
          conn.setAutoCommit(false);
          Statement statement = conn.createStatement();
          LOG.info("Running SQL query: [{}]", sqlQuery);
          ResultSet resultSet = statement.executeQuery(sqlQuery);

          while (resultSet.next()) {
            LOG.info("Result of SQL query: [{}]", resultSet.getString(1));
          }
          LOG.info("testProcedure successful!");
        }catch (Exception e){
          LOG.info("testProcedure error:"+e.getMessage());
        }
      }

    }
}